﻿CREATE PROCEDURE [dbo].[spMSX_create_job]
(
	--- Local / Master Target Parameters
	@is_local_job				bit					= 0,

	--- Job Parameters
	@job_name					nvarchar(128),
	@job_description			nvarchar(512),
	@job_category				nvarchar(128),

	--- Schedule Parameters
	@schedule_name				nvarchar(128),

	--- Step One Parameters
	@step1_name					nvarchar(128),
	@step1_subsystem			nvarchar(40),
	@step1_command				nvarchar(MAX),

	--- Step Two Parameters
	@step2_name					nvarchar(128)		= NULL,
	@step2_subsystem			nvarchar(40)		= NULL,
	@step2_command				nvarchar(MAX)		= NULL,

	--- Step Three Parameters
	@step3_name					nvarchar(128)		= NULL,
	@step3_subsystem			nvarchar(40)		= NULL,
	@step3_command				nvarchar(MAX)		= NULL,

	--- Step Four Parameters
	@step4_name					nvarchar(128)		= NULL,
	@step4_subsystem			nvarchar(40)		= NULL,
	@step4_command				nvarchar(MAX)		= NULL,

	--- Step Five Parameters
	@step5_name					nvarchar(128)		= NULL,
	@step5_subsystem			nvarchar(40)		= NULL,
	@step5_command				nvarchar(MAX)		= NULL
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@jobId uniqueidentifier, @job_type varchar(12), @database_name sysname

--- Pre-process parameters
IF ISNULL(@is_local_job, 1) = 1
  BEGIN
	SELECT	@job_name		= CAST(@job_name + ' - Local' as nvarchar(128)),
			@job_category	= CAST(@job_category + ' - Local' as nvarchar(128)),
			@job_type		= 'LOCAL'
  END
ELSE
  BEGIN
	SELECT	@job_name		= CAST(@job_name + ' - MSX' as nvarchar(128)),
			@job_type		= 'MULTI-SERVER'
  END
SELECT	@database_name		= DB_NAME()


--- Ensure the job category exists
IF NOT EXISTS (SELECT name FROM [msdb].[dbo].[syscategories] WHERE name = @job_category AND category_class=1)
  BEGIN
	EXEC [msdb].[dbo].[sp_add_category]		@class					= 'JOB', 
											@type					= @job_type, 
											@name					= @job_category
  END

--- Create the job
IF NOT EXISTS (SELECT TOP 1 * FROM [msdb].[dbo].[sysjobs] WHERE name = '@job_name')
  BEGIN
	EXEC	[msdb].[dbo].[sp_add_job]			@job_name				= @job_name,
												@enabled				= 1, 
												@notify_level_eventlog	= 0, 
												@notify_level_email		= 0, 
												@notify_level_netsend	= 0, 
												@notify_level_page		= 0, 
												@delete_level			= 0, 
												@description			= @job_description, 
												@category_name			= @job_category, 
												@owner_login_name		= N'sa', 
												@job_id					= @jobId OUTPUT

	--- Add job step 1
	EXEC	[msdb].[dbo].[sp_add_jobstep]		@job_id					= @jobId, 
												@step_name				= @step1_name, 
												@step_id				= 1, 
												@cmdexec_success_code	= 0, 
												@on_success_action		= 3, 
												@on_success_step_id		= 0, 
												@on_fail_action			= 3, 
												@on_fail_step_id		= 0, 
												@retry_attempts			= 0, 
												@retry_interval			= 0, 
												@os_run_priority		= 0, 
												@subsystem				= @step1_subsystem, 
												@command				= @step1_command, 
												@database_name			= @database_name, 
												@flags					= 8

	--- Add job step 2
	IF ISNULL(@step2_name, '') <> ''
	  BEGIN
		EXEC	[msdb].[dbo].[sp_add_jobstep]	@job_id					= @jobId, 
												@step_name				= @step2_name, 
												@step_id				= 2, 
												@cmdexec_success_code	= 0, 
												@on_success_action		= 3, 
												@on_success_step_id		= 0, 
												@on_fail_action			= 3, 
												@on_fail_step_id		= 0, 
												@retry_attempts			= 0, 
												@retry_interval			= 0, 
												@os_run_priority		= 0, 
												@subsystem				= @step2_subsystem, 
												@command				= @step2_command, 
												@database_name			= @database_name, 
												@flags					= 8
	  END

	--- Add job step 3
	IF ISNULL(@step3_name, '') <> ''
	  BEGIN
		EXEC	[msdb].[dbo].[sp_add_jobstep]	@job_id					= @jobId, 
												@step_name				= @step3_name, 
												@step_id				= 3, 
												@cmdexec_success_code	= 0, 
												@on_success_action		= 3, 
												@on_success_step_id		= 0, 
												@on_fail_action			= 3, 
												@on_fail_step_id		= 0, 
												@retry_attempts			= 0, 
												@retry_interval			= 0, 
												@os_run_priority		= 0, 
												@subsystem				= @step3_subsystem, 
												@command				= @step3_command, 
												@database_name			= @database_name, 
												@flags					= 8
	  END

	--- Add job step 4
	IF ISNULL(@step4_name, '') <> ''
	  BEGIN
		EXEC	[msdb].[dbo].[sp_add_jobstep]	@job_id					= @jobId, 
												@step_name				= @step4_name, 
												@step_id				= 4, 
												@cmdexec_success_code	= 0, 
												@on_success_action		= 3, 
												@on_success_step_id		= 0, 
												@on_fail_action			= 3, 
												@on_fail_step_id		= 0, 
												@retry_attempts			= 0, 
												@retry_interval			= 0, 
												@os_run_priority		= 0, 
												@subsystem				= @step4_subsystem, 
												@command				= @step4_command, 
												@database_name			= @database_name, 
												@flags					= 8
	  END

	--- Add job step 5
	IF ISNULL(@step5_name, '') <> ''
	  BEGIN
		EXEC	[msdb].[dbo].[sp_add_jobstep]	@job_id					= @jobId, 
												@step_name				= @step5_name, 
												@step_id				= 5, 
												@cmdexec_success_code	= 0, 
												@on_success_action		= 3, 
												@on_success_step_id		= 0, 
												@on_fail_action			= 3, 
												@on_fail_step_id		= 0, 
												@retry_attempts			= 0, 
												@retry_interval			= 0, 
												@os_run_priority		= 0, 
												@subsystem				= @step5_subsystem, 
												@command				= @step5_command, 
												@database_name			= @database_name, 
												@flags					= 8
	  END

	--- Set step 1 as the starting step
	EXEC	[msdb].[dbo].[sp_update_job]		@job_id					= @jobId, 
												@start_step_id			= 1

	--- Add the job schedule
	EXEC	[msdb].[dbo].[sp_add_jobschedule]	@job_id					= @jobId, 
												@name					= @schedule_name, 
												@enabled				= 1, 
												@freq_type				= 8, 
												@freq_interval			= 2, 
												@freq_subday_type		= 1, 
												@freq_subday_interval	= 0, 
												@freq_relative_interval	= 0, 
												@freq_recurrence_factor	= 1, 
												@active_start_date		= 20110629, 
												@active_end_date		= 99991231, 
												@active_start_time		= 163000, 
												@active_end_time		= 235959

	--- Add the job server, if this is a local job (ONLY)
	IF ISNULL(@is_local_job, 1) = 1
	  BEGIN
		EXEC	[msdb].[dbo].[sp_add_jobserver]	@job_id					= @jobId, 
												@server_name			= N'(local)'
	  END
  END

SET NOCOUNT OFF